"""Optimize existing product tables

Revision ID: 20241005_optimize_existing
Revises: 20241005_optimize_v3
Create Date: 2025-10-05 18:36:00.000000

"""
from typing import Sequence, Union

from alembic import op
import sqlalchemy as sa
from sqlalchemy.dialects import mysql

# revision identifiers, used by Alembic.
revision: str = '20241005_optimize_existing'
down_revision: Union[str, Sequence[str], None] = '20241005_optimize_v3'
branch_labels: Union[str, Sequence[str], None] = None
depends_on: Union[str, Sequence[str], None] = None


def upgrade() -> None:
    """Upgrade schema - Phase 2: Modify existing tables"""
    # ### commands auto generated by Alembic - please adjust! ###

    # 1. Add foreign key constraint to product_categories.parent_id
    try:
        # First, check if the constraint doesn't exist
        op.create_foreign_key(
            'fk_product_categories_parent_id',
            'product_categories',
            'product_categories',
            ['parent_id'],
            ['id'],
            ondelete='CASCADE'
        )
    except Exception:
        # Constraint might already exist, continue
        pass

    # 2. Change is_active field from Integer to Boolean in product_details
    # Using raw SQL because Alembic has issues with MySQL boolean conversion
    op.execute("ALTER TABLE product_details MODIFY is_active TINYINT(1) DEFAULT 1 COMMENT '是否激活'")

    # 3. Change is_main field from Integer to Boolean in product_images
    op.execute("ALTER TABLE product_images MODIFY is_main TINYINT(1) DEFAULT 0 COMMENT '是否主图'")

    # ### end Alembic commands ###


def downgrade() -> None:
    """Downgrade schema"""
    # ### commands auto generated by Alembic - please adjust! ###

    # Revert product_images.is_main to Integer
    op.execute("ALTER TABLE product_images MODIFY is_main INT(11) DEFAULT 0 COMMENT '是否主图：1-主图，0-普通图片'")

    # Revert product_details.is_active to Integer
    op.execute("ALTER TABLE product_details MODIFY is_active INT(11) DEFAULT 1 COMMENT '是否激活：1-激活，0-禁用'")

    # Drop foreign key constraint
    try:
        op.drop_constraint('fk_product_categories_parent_id', 'product_categories', type_='foreignkey')
    except Exception:
        # Constraint might not exist, continue
        pass

    # ### end Alembic commands ###